package com.kayang.webapi.service;

import com.kayang.common.core.UserContext;
import com.kayang.common.db.Database;
import com.kayang.common.db.DatabaseFactory;
import com.kayang.common.exception.ConfigException;
import com.kayang.common.exception.IPException;
import com.kayang.common.exception.ParamException;
import com.kayang.common.misc.WebHelper;
import com.kayang.nadapt.crypt.CryptAES;
import com.kayang.nadapt.table.*;
import com.kayang.nadapt.util.*;
import com.kayang.nadapt.web.HttpContext;
import com.kayang.webapi.config.WebApiConfig;
import com.kayang.webapi.entity.WebAPILog;
import com.kayang.webapi.tool.*;
import net.sf.json.JSON;
import net.sf.json.JSONSerializer;
import net.sf.json.xml.XMLSerializer;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.w3c.dom.Node;

import java.io.IOException;
import java.util.*;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Service
public class DataBizService {
    public final  String sql = "INSERT INTO skyDataWebAPIs_log(FuncID,URID,OPData,OPTime,HostName,LocalUser,OPState,OPText)values(@1,@2,@3,@4,@5,@6,@7,@8)";
    public  Map<Integer, String> msgCollection = new HashMap<Integer, String>();

    @Autowired
    APIdataMannger apIdataMannger;

    /**
     * 检测访问者的IP，确定该机器是否能访问接口
     */
    public  Boolean isCanPassIPFilter(String curIP, String whiteIPS) {
        if ("ALL".equalsIgnoreCase(whiteIPS) || "*".equalsIgnoreCase(whiteIPS)) {
            return true;
        }
        if (StringUtil.isNullOrEmpty(curIP) || StringUtil.isNullOrEmpty(whiteIPS)) {
            return false;
        }

        if (whiteIPS.indexOf(curIP) > -1) {
            return true;
        }

        return false;

    }

    /**
     * 检验当前调用者的登录情况
     *
     * @param account  账户
     * @param password 密码
     * @return SkySecUser
     * @throws Exception
     */

    public  DataView checkUserLogIn(String account, String password) {
        String sqlText = " select * from skysecuser where account = @1 and password= @2 ";
        return DatabaseFactory.getMainDB().query(sqlText, account, password);
    }

    private static ThreadPoolExecutor executor = new ThreadPoolExecutor(2, 10, 60, TimeUnit.SECONDS, new ArrayBlockingQueue<>(10));

    /**
     * 向数据库里插入一条日志记录
     *
     * @param log 日志记录
     * @return String
     * @throws Exception
     */
    public  void writeLog2DB(WebAPILog log) {
        executor.execute(() -> {
            try {
                Database db = DatabaseFactory.getMainDB();
                db.execute(sql, log.getFuncid(), log.getUrid(), log.getOpdata(), log.getOptime(), log.getHostname(), log.getLocaluser(), log.getOpstate(), log.getOptext());
            } catch (Exception ex) {
                LogHelper.error(ex);
                LogHelper.info(StringUtil.format("{0}:\r\n{1}", "日志添加出错：", log.toString()));
                LogHelper.error(StringUtil.format("{0}:\r\n{1}", "日志添加出错：", ex.toString()));
            }
        });

    }

    /**
     * 验证IP是否包含在允许列表内
     *
     * @param ipAddr     本地ip
     * @param ips        配置ip
     * @param methodName 方法名称
     * @return String
     * @throws Exception
     */
    public  Map<String, Object> validateIP(String ipAddr, String ips, String methodName) {
        Map<String, Object> ret = new HashMap<>();
        String str = "";
        boolean flag = isCanPassIPFilter(ipAddr, ips);
        if (!flag) {
            LogHelper.info("接口调用者的地址：" + ipAddr);
            // 创建jesson字符串
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4044);
            msg.setMsg(MsgContent.MsgIP);
            msg.setResult("");
            // java 字符串转json对象
            str = JsonConvert.serializeObject(msg);
            ret.put("msg", str);
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata(ips);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4044);
            log.setOptext(MsgContent.MsgIP);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{0}:\r\n{1}", methodName, log.toString()));
            throw new IPException(MsgCode.Code4044, MsgContent.MsgIP);
        }
        return ret;
    }

    /**
     * 检验接口调用方传递的参数值
     *
     * @param methodName 方法名称
     * @param
     * @param
     * @return String
     * @throws Exception
     */
    @SuppressWarnings("rawtypes")
    public  Map<String, Object> validateVal(String methodName, Map map) {
        Map<String, Object> ret = new HashMap<>();
        String str = "";
        methodName = !StringUtil.isNullOrEmpty(methodName) ? methodName : "";
        if (map == null || map.size() == 0) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4003);
            msg.setMsg(MsgContent.MsgVal);
            msg.setResult("");
            str = JsonConvert.serializeObject(msg);
            ret.put("statu", ParamException.class.getSimpleName());
            ret.put("msg", str);
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata(MsgContent.MsgVal);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4003);
            log.setOptext(MsgContent.MsgVal);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{0}:\r\n{1}", methodName, log.toString()));
            throw new ParamException(MsgCode.Code4003, MsgContent.MsgVal);
        }
        return ret;
    }


    /**
     * 登陆时验证用户名
     *
     * @return String
     * @throws Exception
     */
    public  String validateAccount(Object acc, String json, String methodName) throws Exception {
        String str = "";
        if (acc == null || StringUtil.isNullOrEmpty(acc.toString())) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4020);
            msg.setMsg(MsgContent.MsgAcc);
            msg.setResult("");
            str = JsonConvert.serializeObject(msg);

            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata(json);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4020);
            log.setOptext(MsgContent.MsgAcc);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
        }
        return str;
    }

    /**
     * 登陆时验证用户密码
     *
     * @return String
     * @throws Exception
     */
    public  String validatePwd(Object pwd, String json, String methodName) throws Exception {
        String str = "";
        if (pwd == null) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4021);
            msg.setMsg(MsgContent.MsgPwd);
            msg.setResult("");
            str = JsonConvert.serializeObject(msg);
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata(json);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4021);
            log.setOptext(MsgContent.MsgPwd);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
        }
        return str;
    }

    /**
     * 缓存全局变量
     *
     * @param uc 全局变量对象
     * @return void
     * @throws Exception
     */



    /**
     * 验证AccessToken能否获取到
     *
     * @param token
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @return String
     * @throws Exception
     */
    public  String validateToken(Object token, String json, String methodName) throws Exception {
        String str = "";
        json = !StringUtil.isNullOrEmpty(json) ? json : "";
        if (StringUtil.isNullOrEmpty(token)) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4005);
            msg.setMsg(MsgContent.MsgValidateToken);
            msg.setResult("");
            str = JsonConvert.serializeObject(msg);
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata(json);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4005);
            log.setOptext(MsgContent.MsgValidateToken);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
        }
        return str;
    }

    /**
     * 获取Token成功后
     *
     * @param token
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @param user       登录用户信息
     * @return String
     * @throws Exception
     */
    public  String accessTokenSuccess(String token, String json, String methodName, DataView user) throws Exception {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code0);
        msg.setMsg(MsgContent.MsgToken);
        msg.setResult(token);
        String str = JsonConvert.serializeObject(msg);
        WebAPILog log = new WebAPILog();
        log.setFuncid(-1);
        log.setUrid(user.get(0).getInt("id"));
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(MsgCode.Code0);
        log.setOptext(MsgContent.MsgToken);
        writeLog2DB(log);
        LogHelper.info(StringUtil.format("{3}:\r\n{0};AccessToken:{1};JSON参数:{2}", log.toString(), token, json, methodName));
        return str;
    }

    /**
     * 登录验证，无此用户
     *
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @return String
     * @throws Exception
     */
    public  String noUser(String json, String methodName) throws Exception {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4022);
        msg.setMsg(MsgContent.MsgNoUser);
        msg.setResult("");
        String str = JsonConvert.serializeObject(msg);
        WebAPILog log = new WebAPILog();
        log.setFuncid(-1);
        log.setUrid(-1);
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(MsgCode.Code4022);
        log.setOptext(MsgContent.MsgNoUser);
        writeLog2DB(log);
        LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
        return str;
    }

    /**
     * 获取AccessToken期间出现异常
     *
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @param ex         异常信息
     * @return String
     * @throws Exception
     */
    public  String accessTokenEx(String json, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4023);
        msg.setMsg(MsgContent.MsgUserLogin);
        msg.setResult(ex.toString() + "\r\n" + st);
        String str = JsonConvert.serializeObject(msg);

        WebAPILog log = new WebAPILog();
        log.setFuncid(-1);
        log.setUrid(-1);
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(MsgCode.Code4023);
        log.setOptext(MsgContent.MsgUserLogin);
        writeLog2DB(log);
        LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName), ex);
        return str;
    }

    /**
     * 根据所传入的AccessToken，验证能否获取到内存中的UserContext
     *
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @param uc         缓存信息
     * @return String
     * @throws Exception
     */


    /**
     * 验证FuncId是否被传递进来
     *
     * @param funcId
     * @param json       请求参数
     * @param methodName 请求方法名称
     * @param uc         缓存信息
     * @return String
     * @throws Exception
     */

    public  String validateFuncId(Object funcId, String json, UserContext uc, String methodName) {
        String str = "";
        json = !StringUtil.isNullOrEmpty(json) ? json : "";
        if (StringUtil.isNullOrEmpty(funcId.toString())) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4006);
            msg.setMsg(MsgContent.MsgValidateFuncId);
            msg.setResult("funcID未传入");
            str = JsonConvert.serializeObject(msg);

            WebAPILog log = new WebAPILog();
            if (StringUtil.isNullOrEmpty(funcId)) {
                log.setFuncid(-1);
            } else {
                log.setFuncid(Integer.valueOf(funcId.toString()));
            }
            log.setUrid(uc.getUserID());
            log.setOpdata(json);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(MsgCode.Code4006);
            log.setOptext(MsgContent.MsgValidateFuncId);
            writeLog2DB(log);
            LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
        }
        return str;
    }


    /**
     * @param funcid 业务功能SQL语句编号
     * @param urid   用户id
     * @return
     * @throws Exception
     */
    public  Boolean checkCallPermission(String funcid, int urid, String ip) {
        if (StringUtil.isNullOrEmpty(funcid)) return true;
        DataView dv = apIdataMannger.getIpList(Convert.toInt32(funcid), urid);
        if (dv != null && dv.size() > 0) {
            String ipList = dv.get(0).getString("IP");
            if("ALL".equalsIgnoreCase(ipList) || "*".equalsIgnoreCase(ipList)) return true;

            String[] ips = ipList.split(";");
            for (String cip : ips) {
                if(cip.trim().equals(ip)){
                    return true;
                }
            }
            return false;
        }
        return false;
    }

    /**
     * DataView转json格式
     */
    public  String jsonSerialize(DataView dataView) throws Exception {
        String output = "";

        if (dataView.size() >= 1) {

            StringBuffer outer = new StringBuffer();
            outer.append("[");
            for (DataRowView row : dataView) {
                outer.append("{");
                StringBuffer inner = new StringBuffer();
                for (DataColumn colName : row.getColumns()) {
                    String colValue = "";
                    if (!StringUtil.isNullOrEmpty(row.get(colName.getColumnName()))) {
                        colValue = row.get(colName.getColumnName()).toString();
                    } else {
                        colValue = "";
                    }
                    if (inner.length() >= 1)
                        inner.append(",");
                    inner.append(StringUtil.format(" \"{0}\":\"{1}\"", StringUtil.format(colName.getColumnName()), StringUtil.format(colValue)));
                }
                outer.append(inner.toString());
                outer.append("},");
            }
            output = StringUtil.trimEnd(outer.toString(), ",") + "]";
        }
        return output;
    }

    /**
     * 按照指定格式读取数据 返回指定格式的字符串，内容是JSON或者XML
     *
     * @param funcId   业务功能SQL语句编号
     * @param format   格式
     * @param dataPart
     * @param paras    业务功能SQL中参数值
     * @param uc
     * @param json
     * @return String
     * @throws Exception
     */
    @SuppressWarnings({"unused", "unchecked", "rawtypes"})
    public  String getDataByFormat(String funcId, Object format, Object dataPart, Map<String, Object> paras, UserContext uc, String json) throws Exception {
        HttpMsg msg = new HttpMsg();
        WebAPILog log = new WebAPILog();
        String ret = "";
        Database db = DatabaseFactory.getMainDB();
        XMLSerializer xmlSerializer = new XMLSerializer();
        xmlSerializer.setRootName("Root");
        xmlSerializer.setTypeHintsEnabled(false);
        String methodName = "GetData-->GetDataByFormat";
        format = StringUtil.isNullOrEmpty(format) ? "json" : format.toString();
        dataPart = StringUtil.isNullOrEmpty(dataPart) ? "D" : dataPart.toString();
        DataView dv = apIdataMannger.getAPI(Convert.toInt32(funcId));
        String datasql = "";
        int pageSize = 0, pageNum = 0, pageCount = 0;
        try {
            if (dv.size() > 0) {
                datasql = dv.get(0).getString("dataSource");
                if (StringUtil.isNullOrEmpty(datasql)) {
                    msg.setMsgId(MsgCode.Code4002);
                    msg.setMsg(MsgContent.MsgMainDataNoSqlStament);
                    msg.setResult("");
                    ret = JsonConvert.serializeObject(msg);
                    log.setFuncid(Integer.valueOf(funcId.toString()));
                    log.setUrid(uc.getUserID());
                    log.setOpdata(json);
                    log.setOptime(new Date());
                    log.setHostname(SystemUtil.getMachineName());
                    log.setLocaluser(SystemUtil.getUserName());
                    log.setOpstate(MsgCode.Code4002);
                    log.setOptext(MsgContent.MsgMainDataNoSqlStament);
                    writeLog2DB(log);
                    LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), json, methodName));
                    if (format.toString().toUpperCase().trim().equals("XML")){
                        JSON json1 = JSONSerializer.toJSON(msg);
                        ret = xmlSerializer.write(json1);
                    }
                    return ret;
                }
                if (isSingleDataSource(datasql)) { // select 1
                    String objKey = "", jsonStr = "", jsonSchema = "";
                    DataView res = null;
                    try {
                        if (!StringUtil.isNullOrEmpty(dv.get(0).getString("objKey"))) {
                            objKey = dv.get(0).getString("objKey");
                        } else {
                            objKey = "Object";
                        }
                        SqlScript s = new SqlScript(datasql);
                        s = fillUserVariables(s, uc);
                        s = fillCustomerVariable(s, paras);
                        String sqlText = s.getSqlTxt().toString();
                        List list = setSqlParas(s);
                        //响应数据分页处理， pageSize pageBum 来自于paras
                        pageSize = Convert.toInt32(paras.get("PAGESIZE"));
                        pageNum = Convert.toInt32(paras.get("PAGENUM"));
                        String xsql = "";
                        if (pageSize > 0 && pageNum > 0) {
                            xsql = "select count(*) from  (" + sqlText + ") A";
                            res = db.query(xsql, list);
                            int count = Integer.parseInt(StringUtil.format("{0}", res.get(0).get(0)));
                            pageCount = count / pageSize;
                            if (count % pageSize > 0) {
                                pageCount += 1;
                            }
                            if (pageNum > pageCount) {
                                pageNum = pageCount;
                            }
                            int startPoint = (pageNum - 1) * pageSize;
                            int endPoint = pageNum * pageSize;
                            if (db.getDBType().isOracle()) {
                                xsql = "select * from (select rownum kayangwebapi_pageinfo, A.* from (" + sqlText + ") A where rownum <= " + endPoint + ") where kayangwebapi_pageinfo > " + startPoint;

                            } else if (db.getDBType().isSqlServer()) {
                                xsql = sqlText;
                            } else {
                                xsql = sqlText;
                            }
                        } else {
                            xsql = sqlText;
                        }

                        res = db.query(xsql, list);
                    } catch (Exception e) {
                        log.setFuncid(Integer.valueOf(funcId.toString()));
                        log.setUrid(uc.getUserID());
                        log.setOpdata(json);
                        log.setOptime(new Date());
                        log.setHostname(SystemUtil.getMachineName());
                        log.setLocaluser(SystemUtil.getUserName());
                        log.setOpstate(MsgCode.Code4010);
                        log.setOptext("   GetDataByFormat 单数据源 FillUserVariables方法出现异常");
                        writeLog2DB(log);
                        LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "GetDataByFormat-935", new Date().toString()));
                        LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "GetDataByFormat-935", e.toString(), new Date().toString()));

                    }
                    if (res != null && res.size() > 0) {
                        if (format.toString().toUpperCase().trim().equals("JSON")) {
                            StringBuilder sb = new StringBuilder();
                            sb.append("{");
                            sb.append("\"MsgId\":0,");
                            sb.append("\"Msg\":\"Success(读取数据成功！)\",");
                            sb.append("\"Result\":{");
                            sb.append(StringUtil.format("\"{0}\":", objKey));
                            sb.append("{");
                            if (dataPart.toString().toUpperCase().trim().equals("D")) {
                                sb.append("\"pageinfo\":");
                                sb.append("{");
                                sb.append("\"pageNum\":" + pageNum + ",");
                                sb.append("\"pageSize\":" + pageSize + ",");
                                sb.append("\"pageCount\":" + pageCount);
                                sb.append("},");
                                sb.append("\"Row\":");
                                try {
                                    jsonStr = jsonSerialize(res);
                                } catch (Exception e) {
                                    log.setFuncid(Integer.valueOf(funcId.toString()));
                                    log.setUrid(uc.getUserID());
                                    log.setOpdata(json);
                                    log.setOptime(new Date());
                                    log.setHostname(SystemUtil.getMachineName());
                                    log.setLocaluser(SystemUtil.getUserName());
                                    log.setOpstate(MsgCode.Code4010);
                                    log.setOptext("   GetDataByFormat 单数据源 jsonSerialize方法出现异常");
                                    writeLog2DB(log);
                                    LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "GetDataByFormat-962", new Date().toString()));
                                    LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "GetDataByFormat-962", e.toString(), new Date().toString()));
                                }
                                int i = 0;
                                sb.append(jsonStr);
                            } else if (dataPart.toString().toUpperCase().trim().equals("S")) {
                                sb.append("\"Schema\":");
                                try {
                                    jsonSchema = getDataTableSchema4Json(res.getTable());
                                } catch (Exception e) {
                                    log.setFuncid(Integer.valueOf(funcId.toString()));
                                    log.setUrid(uc.getUserID());
                                    log.setOpdata(json);
                                    log.setOptime(new Date());
                                    log.setHostname(SystemUtil.getMachineName());
                                    log.setLocaluser(SystemUtil.getUserName());
                                    log.setOpstate(MsgCode.Code4010);
                                    log.setOptext("   GetDataByFormat 单数据源 GetDataTableSchema4Json方法出现异常");
                                    writeLog2DB(log);
                                    LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "GetDataByFormat-982", new Date().toString()));
                                    LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "GetDataByFormat-982", e.toString(), new Date().toString()));
                                }
                                sb.append(jsonSchema);
                            } else {
                                sb.append("\"Schema\":");
                                try {
                                    jsonSchema = getDataTableSchema4Json(res.getTable());
                                } catch (Exception e) {
                                    log.setFuncid(Integer.valueOf(funcId.toString()));
                                    log.setUrid(uc.getUserID());
                                    log.setOpdata(json);
                                    log.setOptime(new Date());
                                    log.setHostname(SystemUtil.getMachineName());
                                    log.setLocaluser(SystemUtil.getUserName());
                                    log.setOpstate(MsgCode.Code4010);
                                    log.setOptext("   GetDataByFormat 单数据源 GetDataTableSchema4Json方法出现异常");
                                    writeLog2DB(log);
                                    LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "GetDataByFormat--999", new Date().toString()));
                                    LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "GetDataByFormat--999", e.toString(), new Date().toString()));
                                }
                                sb.append(jsonSchema);
                                sb.append(",");
                                sb.append("\"Row\":");
                                try {
                                    jsonStr = jsonSerialize(res);
                                } catch (Exception e) {
                                    log.setFuncid(Integer.valueOf(funcId.toString()));
                                    log.setUrid(uc.getUserID());
                                    log.setOpdata(json);
                                    log.setOptime(new Date());
                                    log.setHostname(SystemUtil.getMachineName());
                                    log.setLocaluser(SystemUtil.getUserName());
                                    log.setOpstate(MsgCode.Code4010);
                                    log.setOptext("   GetDataByFormat 单数据源 jsonSerialize方法出现异常");
                                    writeLog2DB(log);
                                    LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "GetDataByFormat--1018", new Date().toString()));
                                    LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "GetDataByFormat--1018", e.toString(), new Date().toString()));

                                }
                                sb.append(jsonStr);
                            }
                            sb.append("}"); // 匹配ObjKey后面的{
                            sb.append("}"); // 匹配result后面的{
                            if (!StringUtil.isNullOrEmpty(dv.get(0).getString("DateCount"))) {
                                sb.append(",\"count\":");
                                String countsql = dv.get(0).getString("DateCount");
                                SqlScript counts = new SqlScript(countsql);
                                counts = fillUserVariables(counts, uc);
                                counts = fillCustomerVariable(counts, paras);
                                String sqlText = counts.getSqlTxt().toString();
                                List list = setSqlParas(counts);
                                Object count = db.queryScalar(sqlText, list);
                                sb.append("\"").append(count).append("\"");
                            }
                            sb.append("}"); // 匹配整个开始的{
                            ret = sb.toString().replace("\r\n", "\\r\\n").replace("\t", "\\t");

                        } else if (format.toString().toUpperCase().trim().equals("XML")) {
                            StringBuilder sbXml = new StringBuilder();
                            sbXml.append("<Root>");
                            sbXml.append("<MsgId>0</MsgId>");
                            sbXml.append("<Msg>Success(读取数据成功！)</Msg>");
                            sbXml.append("<Result>");
                            sbXml.append(StringUtil.format("<{0}>", objKey));
                            if (dataPart.toString().toUpperCase().trim().equals("D")) {
                                String valXml = Converter.dataTable2XML(res.getTable(), "Row");
                                sbXml.append(valXml);
                            } else if (dataPart.toString().toUpperCase().trim().equals("S")) {
                                sbXml.append("<Schema>");
                                String schema = getDataTableSchema(res.getTable());
                                sbXml.append(schema);
                                sbXml.append("</Schema>");
                            } else {
                                sbXml.append("<Schema>");
                                String schema = getDataTableSchema(res.getTable());
                                sbXml.append(schema);
                                sbXml.append("</Schema>");
                                String valXml = Converter.dataTable2XML(res.getTable(), "");
                                sbXml.append(valXml);
                            }
                            sbXml.append(StringUtil.format("</{0}>", objKey));
                            sbXml.append("</Result>");
                            sbXml.append("</Root>");
                            ret = sbXml.toString();
                        }
                    } else {
                        msg.setMsgId(MsgCode.Code4045);
                        msg.setMsg(MsgContent.MsgSimpleNoRecords);
                        msg.setResult("");
                        ret = JsonConvert.serializeObject(msg);
                        if (format.toString().toUpperCase().trim().equals("XML")){
                            JSON json1 = JSONSerializer.toJSON(msg);
                            ret = xmlSerializer.write(json1);
                        }
                        log.setFuncid(Integer.valueOf(funcId));
                        log.setUrid(uc.getUserID());
                        log.setOpdata(json);
                        log.setOptime(new Date());
                        log.setHostname(SystemUtil.getMachineName());
                        log.setLocaluser(SystemUtil.getUserName());
                        log.setOpstate(MsgCode.Code4045);
                        log.setOptext(MsgContent.MsgSimpleNoRecords);
                        writeLog2DB(log);
                        LogHelper.info(StringUtil.format("{2}:\r\n{0};JSON参数:{1}", log.toString(), !StringUtil.isNullOrEmpty(json) ? json : "", methodName));
                    }
                } else // 非单数据源 // <DataSource>
                {
                    LogHelper.info("进入dataSourse方法 开始解析:::");
                    MyDataSource dSource = null;
                    String xsql;
                    SqlScript s;
                    DataView dvM = null;
                    try {
                        dSource = parseComplexDataSource(datasql);
                        xsql = dSource.getDataSql();
                        s = new SqlScript(xsql);
                        s = fillUserVariables(s, uc);
                        s = fillCustomerVariable(s, paras);
                        dvM = db.query(s.getSqlTxt(), setSqlParas(s));
                    } catch (Exception e) {
                        LogHelper.info(StringUtil.format("  DataBiz 非单数据源 FillCustomerVariable方法出现异常 ;\r\n 时间 : {0};", new Date().toString()));
                        LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2};", methodName, e.toString(), new Date().toString()));
                    }
                    if (dvM != null && dvM.size() > 0) {
                        if (format.toString().toUpperCase().trim().equals("JSON")) {
                            StringBuilder sb = new StringBuilder();
                            sb.append("{");
                            sb.append("\"MsgId\":0,");
                            sb.append("\"Msg\":\"Success(读取数据成功！)\",");
                            sb.append("\"Result\":{");
                            sb.append(StringUtil.format("\"{0}\":", dSource.getObjKey()));
                            sb.append("{");
                            if (dataPart.toString().toUpperCase().trim().equals("D")) {
                                sb.append("\"Row\":");
                                sb.append("[");
                                for (int i = 0; i < dvM.size(); i++) {
                                    DataTable dt = null;
                                    String jsonStr = null;
                                    try {
                                        dt = dvM.getTable();
                                        Map<String, Object> map = dt.getDefaultView().get(i).getData();
                                        jsonStr = JsonConvert.serializeObject(map);
                                    } catch (Exception e) {
                                        LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", methodName, new Date().toString()));
                                        LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", methodName, e.toString(), new Date().toString()));
                                        log.setFuncid(Integer.valueOf(funcId.toString()));
                                        log.setUrid(uc.getUserID());
                                        log.setOpdata(json);
                                        log.setOptime(new Date());
                                        log.setHostname(SystemUtil.getMachineName());
                                        log.setLocaluser(SystemUtil.getUserName());
                                        log.setOpstate(MsgCode.Code4010);
                                        log.setOptext("   DataBiz 非单数据源 jsonSerialize方法出现异常");
                                        writeLog2DB(log);
                                    }
                                    if (StringUtil.isNullOrEmpty(jsonStr)) {
                                        sb.append("");
                                    } else {
                                        jsonStr = rightTrim((leftTrim(jsonStr, "[")), "]");
                                        jsonStr = rightTrim(jsonStr.trim(), "}");
                                        sb.append(jsonStr);
                                    }
                                    sb.append(",");
                                    sb.append("\"Details\":");
                                    sb.append("{");
                                    // details开始标签
                                    for (int j = 0; j < dSource.getChildren().size(); j++) {
                                        MyDataSource cItem = dSource.getChildren().get(j);
                                        sb.append(StringUtil.format("\"{0}\":", cItem.ObjKey));
                                        xsql = cItem.getDataSql();
                                        s = new SqlScript(xsql);
                                        s = fillUserVariables(s, uc);
                                        s = fillCustomerVariable(s, paras);
                                        DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                        DataRow dr = dt.getRows().get(i);
                                        String filter = "";
                                        if (!StringUtil.isNullOrEmpty(cItem.getDataFilter())) {
                                            filter = handleDataFilter(cItem.getDataFilter(), uc, paras, dr);
                                        }
                                        dvC.setRowFilter(filter); // 待考证
                                        String cJsonStr = jsonSerialize(dvC);
                                        if (StringUtil.isNullOrEmpty(cJsonStr)) {
                                            sb.append("[]");
                                        } else {
                                            sb.append(cJsonStr);
                                        }
                                        if (j != dSource.getChildren().size() - 1) {
                                            sb.append(",");
                                        }
                                    }
                                    sb.append("}"); // details闭合标签
                                    sb.append("}"); // jsonStr =
                                    // jsonStr.Trim().TrimEnd('}');
                                    // 的闭合标签
                                    if (i != dvM.size() - 1) {
                                        sb.append(",");
                                    }
                                }
                                sb.append("]");
                            } else if (dataPart.toString().toUpperCase().trim().equals("S")) {
                                sb.append("\"Schema\":");
                                String jsonSchema = getDataTableSchema4Json(dvM.getTable());
                                sb.append(jsonSchema);
                                sb.append(',');

                                for (int j = 0; j < dSource.getChildren().size(); j++) {
                                    MyDataSource cItem = dSource.getChildren().get(j);
                                    sb.append(StringUtil.format("\"{0}\":", cItem.getObjKey()));
                                    xsql = cItem.getDataSql();
                                    s = new SqlScript(xsql);
                                    s = fillUserVariables(s, uc);
                                    s = fillCustomerVariable(s, paras);
                                    DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                    // xsql = ReplaceSysVariable(xsql, uc);
                                    // xsql = ReplaceCustomerVariable2(xsql,
                                    // paras);
                                    // DataView dvC = db.Query(xsql);

                                    String cJsonSchema = getDataTableSchema4Json(dvC.getTable());
                                    sb.append(cJsonSchema);
                                    if (j != dSource.getChildren().size() - 1) {
                                        sb.append(",");
                                    }
                                }
                            } else {
                                sb.append("\"Schema\":");
                                String jsonSchema = getDataTableSchema4Json(dvM.getTable());
                                sb.append(jsonSchema);
                                sb.append(',');

                                for (MyDataSource cItem : dSource.getChildren()) {
                                    sb.append(StringUtil.format("\"{0}\":", cItem.getObjKey()));
                                    xsql = cItem.getDataSql();
                                    s = new SqlScript(xsql);
                                    s = fillUserVariables(s, uc);
                                    s = fillCustomerVariable(s, paras);
                                    DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                    // xsql = ReplaceSysVariable(xsql, uc);
                                    // xsql = ReplaceCustomerVariable2(xsql,
                                    // paras);
                                    // DataView dvC = db.Query(xsql);

                                    String cJsonSchema = getDataTableSchema4Json(dvC.getTable());
                                    sb.append(cJsonSchema);
                                    sb.append(",");
                                }

                                sb.append("\"Row\":");
                                sb.append("[");
                                for (int i = 0; i < dvM.size(); i++) {
                                    DataTable dt = dvM.getTable();
                                    String jsonStr = jsonSerialize(dvM);
                                    if (StringUtil.isNullOrEmpty(jsonStr)) {
                                        sb.append("");
                                    } else {
                                        jsonStr = rightTrim((leftTrim(jsonStr, "[")), "]");
                                        jsonStr = rightTrim(jsonStr.trim(), "}");
                                        sb.append(jsonStr);
                                    }

                                    sb.append(",");
                                    sb.append("\"Details\":");
                                    sb.append("{"); // details开始标签
                                    for (int j = 0; j < dSource.getChildren().size(); j++) {
                                        MyDataSource cItem = dSource.getChildren().get(j);
                                        sb.append(StringUtil.format("\"{0}\":", cItem.ObjKey));
                                        xsql = cItem.getDataSql();
                                        s = new SqlScript(xsql);
                                        s = fillUserVariables(s, uc);
                                        s = fillCustomerVariable(s, paras);
                                        DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                        // xsql = ReplaceSysVariable(xsql, uc);
                                        // xsql = ReplaceCustomerVariable2(xsql,
                                        // paras);
                                        // DataView dvC = db.Query(xsql);
                                        DataRow dr = dvM.get(i).getRow();
                                        String filter = "";
                                        if (!StringUtil.isNullOrEmpty(cItem.getDataFilter())) {
                                            filter = handleDataFilter(cItem.DataFilter, uc, paras, dr);
                                        }

                                        dvC.setRowFilter(filter); // 待考证
                                        DataTable cTable = dvC.getTable();
                                        String cJsonStr = jsonSerialize(dvC);
                                        if (StringUtil.isNullOrEmpty(cJsonStr)) {
                                            sb.append("");
                                        } else {
                                            sb.append(cJsonStr);
                                        }
                                        if (j != dSource.getChildren().size() - 1) {
                                            sb.append(",");
                                        }
                                    }
                                    sb.append("}"); // details闭合标签
                                    sb.append("}"); // jsonStr =
                                    // jsonStr.Trim().TrimEnd('}');
                                    // 的闭合标签
                                    if (i != dvM.size() - 1) {
                                        sb.append(",");
                                    }
                                }
                                sb.append("]");
                            }
                            sb.append("}"); // 匹配ObjKey后面的{
                            sb.append("}"); // 匹配result后面的{
                            if (!StringUtil.isNullOrEmpty(dv.get(0).getString("datecount"))) {
                                sb.append(",\"count\":");
                                String countsql = dv.get(0).getString("DateCount");
                                SqlScript counts = new SqlScript(countsql);
                                counts = fillUserVariables(counts, uc);
                                counts = fillCustomerVariable(counts, paras);
                                String sqlText = counts.getSqlTxt().toString();
                                List list = setSqlParas(counts);
                                Object count = db.queryScalar(sqlText, list);
                                sb.append("\"").append(count).append("\"");
                            }
                            sb.append("}"); // 匹配整个开始的{
                            ret = sb.toString();

                        } else if (format.toString().toUpperCase().trim().equals("XML")) {
                            StringBuilder sbXml = new StringBuilder();
                            sbXml.append("<Root>");
                            sbXml.append("<MsgId>0</MsgId>");
                            sbXml.append("<Msg>Success(读取数据成功！)</Msg>");
                            sbXml.append("<Result>");
                            sbXml.append(StringUtil.format("<{0}>", dSource.getObjKey()));

                            if (dataPart.toString().toUpperCase().trim().equals("D")) {
                                for (DataRowView item : dvM) {
                                    DataTable dt = dvM.getTable();
                                    String rowXml = Converter.dataTable2XML(dt, "");
                                    sbXml.append("<Row>"); // 主数据源的行 开始 标签
                                    sbXml.append(rowXml);
                                    sbXml.append("<Details>");
                                    for (MyDataSource cItem : dSource.getChildren()) {
                                        sbXml.append(StringUtil.format("<{0}>", cItem.getObjKey()));
                                        xsql = cItem.getDataSql();
                                        s = new SqlScript(xsql);
                                        s = fillUserVariables(s, uc);
                                        s = fillCustomerVariable(s, paras);
                                        DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                        // xsql = ReplaceSysVariable(xsql, uc);
                                        // xsql = ReplaceCustomerVariable2(xsql,
                                        // paras);
                                        // DataView dvC = db.Query(xsql);
                                        String filter = "";
                                        DataRow dr = item.getRow();
                                        if (!StringUtil.isNullOrEmpty(cItem.getDataFilter())) {
                                            filter = handleDataFilter(cItem.getDataFilter(), uc, paras, dr);
                                        }
                                        dvC.setRowFilter(filter); // 待考证
                                        String cXml = Converter.dataView2XML(dvC, "Row");
                                        sbXml.append(cXml);
                                        sbXml.append(StringUtil.format("</{0}>", cItem.getObjKey()));
                                    }
                                    sbXml.append("</Details>");
                                    sbXml.append("</Row>"); // 主数据源的行 结束 标签
                                }
                            } else if (dataPart.toString().toUpperCase().trim().equals("S")) {
                                sbXml.append("<Schema>");
                                String schema = getDataTableSchema(dvM.getTable());
                                sbXml.append(schema);

                                for (MyDataSource cItem : dSource.getChildren()) {
                                    sbXml.append(StringUtil.format("<{0}>", cItem.getObjKey()));
                                    xsql = cItem.getDataSql();
                                    s = new SqlScript(xsql);
                                    s = fillUserVariables(s, uc);
                                    s = fillCustomerVariable(s, paras);
                                    DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                    // xsql = ReplaceSysVariable(xsql, uc);
                                    // xsql = ReplaceCustomerVariable2(xsql,
                                    // paras);
                                    // DataView dvC = db.Query(xsql);

                                    String schemaC = getDataTableSchema(dvC.getTable());
                                    sbXml.append(schemaC);

                                    sbXml.append(StringUtil.format("</{0}>", cItem.getObjKey()));
                                }
                                sbXml.append("</Schema>");
                            } else {
                                sbXml.append("<Schema>");
                                String schema = getDataTableSchema(dvM.getTable());
                                sbXml.append(schema);

                                for (MyDataSource cItem : dSource.getChildren()) {
                                    sbXml.append(StringUtil.format("<{0}>", cItem.getObjKey()));
                                    xsql = cItem.getDataSql();
                                    s = new SqlScript(xsql);
                                    s = fillUserVariables(s, uc);
                                    s = fillCustomerVariable(s, paras);
                                    DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                    // xsql = ReplaceSysVariable(xsql, uc);
                                    // xsql = ReplaceCustomerVariable2(xsql,
                                    // paras);
                                    // DataView dvC = db.Query(xsql);
                                    String schemaC = getDataTableSchema(dvC.getTable());
                                    sbXml.append(schemaC);
                                    sbXml.append(StringUtil.format("</{0}>", cItem.getObjKey()));
                                }
                                sbXml.append("</Schema>");
                                for (DataRowView item : dvM) {
                                    DataTable dt = dvM.getTable();
                                    String rowXml = Converter.dataTable2XML(dt, "");
                                    sbXml.append("<Row>"); // 主数据源的行 开始 标签
                                    sbXml.append(rowXml);
                                    sbXml.append("<Details>");
                                    for (MyDataSource cItem : dSource.getChildren()) {
                                        sbXml.append(StringUtil.format("<{0}>", cItem.getObjKey()));
                                        xsql = cItem.getDataSql();
                                        s = new SqlScript(xsql);
                                        s = fillUserVariables(s, uc);
                                        s = fillCustomerVariable(s, paras);
                                        DataView dvC = db.query(s.getSqlTxt(), setSqlParas(s));
                                        // xsql = ReplaceSysVariable(xsql, uc);
                                        // xsql = ReplaceCustomerVariable2(xsql,
                                        // paras);
                                        // DataView dvC = db.Query(xsql);
                                        String filter = "";
                                        DataRow dr = item.getRow();
                                        if (!StringUtil.isNullOrEmpty(cItem.getDataFilter())) {
                                            filter = handleDataFilter(cItem.getDataFilter(), uc, paras, dr);
                                        }
                                        DataColumnCollection list = dv.getColumns();
                                        boolean bool = false;
                                        for (DataColumn column : list) {
                                            if (filter.equals(column.getColumnName())) { // filter
                                                // 待考证
                                                bool = true;
                                            }
                                        }
                                        String cXml = Converter.dataView2XML(dvC, "Row");
                                        sbXml.append(cXml);
                                        sbXml.append(StringUtil.format("</{0}>", cItem.getObjKey()));
                                    }
                                    sbXml.append("</Details>");
                                    sbXml.append("</Row>"); // 主数据源的行 结束 标签
                                }
                            }
                            sbXml.append(StringUtil.format("</{0}>", dSource.getObjKey()));
                            sbXml.append("</Result>");
                            sbXml.append("</Root>");
                            ret = sbXml.toString();
                        }

                    } else {
                        // 主数据源没有记录
                        msg.setMsgId(MsgCode.Code4001);
                        msg.setMsg(MsgContent.MsgMainDataNoRecords);
                        msg.setResult("");
                        // java 字符串转json对象
                        // JSONObject jsonStu = JSONObject.fromObject(msg);
                        ret = JsonConvert.serializeObject(msg);
                        if (format.toString().toUpperCase().trim().equals("XML")){
                            JSON json1 = JSONSerializer.toJSON(msg);
                            ret = xmlSerializer.write(json1);
                        }
                    }
                }
            }
        } catch (Exception e) {
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", methodName, new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", methodName, e.toString(), new Date().toString()));
            e.printStackTrace();
        }
        return ret;
    }

//    public static void main(String[] args) {
//        HttpMsg msg = new  HttpMsg();
//        msg.setMsgId(MsgCode.Code4001);
//        msg.setMsg(MsgContent.MsgMainDataNoRecords);
//        msg.setResult("");
//        XMLSerializer xmlSerializer = new XMLSerializer();
//        xmlSerializer.setRootName("Root");
//        xmlSerializer.setTypeHintsEnabled(false);
//        JSON json1 = JSONSerializer.toJSON(msg);
//        String ret = xmlSerializer.write(json1);
//        System.out.println(ret);
//    }

    /**
     * 填充主数据源表格里的变量
     *
     * @param script SqlScript对象
     * @param dr     DataTable数据
     * @return String
     */
    public  SqlScript fillMainDataVariable(SqlScript script, DataRow dr) {
        Map<String, SqlScript.SqlParaPair> sqlPs = script.getSqlParas();
        Set<String> keys = sqlPs.keySet();
        for (String key : keys) {
            String colName = rightTrim(leftTrim(sqlPs.get(key).getParaName().trim(), "["), "]");
            if (!StringUtil.isNullOrEmpty(dr.getValue(colName))) {
                sqlPs.get(key).setParaValue(dr.getValue(colName));
            }
        }
        return script;
    }

    /**
     * 按照主数据来过滤从数据
     *
     * @param dataFilter 过滤
     * @param paras      业务功能SQL中参数值 参数
     * @param
     * @param uc
     * @return String
     */
    private  String handleDataFilter(String dataFilter, UserContext uc, Map<String, Object> paras, DataRow dr) {
        // dataFilter = ReplaceSysVariable(dataFilter, uc);
        // dataFilter = ReplaceCustomerVariable2(dataFilter, paras);
        // dataFilter = ReplaceMainDataVariable(dataFilter, dt);
        // return dataFilter;
        SqlScript s = null;
        String filter = "";
        try {
            s = new SqlScript(dataFilter);
            s = fillUserVariables(s, uc);
            s = fillCustomerVariable(s, paras);
            s = fillMainDataVariable(s, dr);
        } catch (Exception e) {
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(MsgCode.Code4053);
            msg.setMsg(MsgContent.DataFilter);
            msg.setResult("");
            filter = JsonConvert.serializeObject(msg);
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "HandleDataFilter", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "HandleDataFilter", e.toString(), new Date().toString()));

            e.printStackTrace();
            return filter;
        }

        for (String key : s.getSqlParas().keySet()) {
            Object name = s.getSqlParas().get(key).getParaValue();
            if (!StringUtil.isNullOrEmpty(name)) {
                filter = s.SqlTxt.replace(key, StringUtil.format("{0}", s.getSqlParas().get(key).getParaValue().toString()));
                /*
                 * } else { filter = s.SqlTxt.replace(key,
                 * StringUtil.format("N'{0}'",
                 * s.getSqlParas().get(key).getParaValue().toString()));
                 */
            }
        }
        return filter;
    }

    /**
     * 获取数据表的架构信息[XML字符串]
     *
     * @param
     * @return 返回一个xml字符串，该字符串代表的是所指定表的架构信息
     */
    @SuppressWarnings("unused")
    private  String getDataTableSchema(DataTable dt) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (DataColumn item : dt.getColumns()) {
            sb.append("<Column>");
            sb.append(StringUtil.format("<Name>{0}</Name>", item.getColumnName()));
            sb.append(StringUtil.format("<Type>{0}</Type>", item.getDataType().toString()));
            // sb.AppendFormat("<Size>{0}</Size>", oItem.MaxLength);
            // //这一列的值总是为-1，不管DB里有没有长度，和杨董商量后，他建议先去掉该列
            sb.append("</Column>");
        }
        return sb.toString();
    }

    /**
     * 获取数据表的架构信息[JSON字符串]
     *
     * @param
     * @return 返回一个json字符串，该字符串代表的是所指定表的架构信息
     */
    private  String getDataTableSchema4Json(DataTable dt) throws Exception {
        StringBuilder sb = new StringBuilder();
        sb.append("[");
        for (DataColumn item : dt.getColumns()) {
            sb.append("{");
            sb.append(StringUtil.format("\"Name\":\"{0}\"", item.getColumnName()));
            sb.append(",");
            sb.append(StringUtil.format("\"Type\":\"{0}\"", item.getDataType().toString()));
            sb.append("}");
            sb.append(",");
        }
        sb = new StringBuilder(StringUtil.trimEnd(sb.toString(), ","));
        sb.append("]");
        return sb.toString();
    }

    /**
     * 解析主从数据源里的SQL语句
     *
     * @param xmlDataSource sql源
     * @return MyDataSource
     */
    private  MyDataSource parseComplexDataSource(String xmlDataSource) {
        MyDataSource mySource = null;
        try {
            mySource = new MyDataSource();
            Document document = DocumentHelper.parseText(xmlDataSource);
            // Element root = document.getRootElement();

            // 获得指定节点下面的子节点
            Element xe = (Element) document.selectSingleNode("DataSource");
            mySource.setObjKey(xe.attributeValue("ObjKey"));
            mySource.setTitle(xe.attributeValue("title"));
            mySource.setDataFilter(xe.attributeValue("DataFilter")); // 主数据源就不配置过滤条件
            mySource.setPKey(xe.attributeValue("pKey"));

            Element xeSql = (Element) xe.selectSingleNode("DataSQL");
            mySource.setDataSql(xeSql.getText());

            List<MyDataSource> dsList = new ArrayList<MyDataSource>();
            @SuppressWarnings("unchecked")
            List<Node> roots = xe.selectNodes("DataSource");
            Iterator<Node> it = roots.iterator();
            while (it.hasNext()) {
                MyDataSource child = new MyDataSource();
                Element xEle = (Element) it.next();
                child.setObjKey(xEle.attributeValue("ObjKey"));
                child.setTitle(xEle.attributeValue("Title"));
                child.setDataFilter(xEle.attributeValue("DataFilter"));
                child.setPKey(xEle.attributeValue("PKey"));
                Element xEleSql = (Element) xEle.selectSingleNode("DataSQL");
                child.setDataSql(xEleSql.getText());
                dsList.add(child);
            }
            mySource.Children = dsList;
        } catch (Exception e) {
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata("");
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(0);
            log.setOptext("ParseComplexDataSource方法出现异常");
            writeLog2DB(log);
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "ParseComplexDataSource", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "ParseComplexDataSource", e.toString(), new Date().toString()));
            e.printStackTrace();
        }
        return mySource;
    }

    /**
     * 设置Sql语句的值
     * <p>
     * sql对象
     *
     * @return List
     */

    public  List<Object> setSqlParas(SqlScript s) throws Exception {
        List<Object> list = new ArrayList<Object>();
        Map<String, SqlScript.SqlParaPair> sqlPs = s.getSqlParas();
        Set<String> keys = sqlPs.keySet();
        Object[] result = Arrays.stream(keys.toArray()).map(key -> Convert.toInt32(key.toString().substring(1))).toArray();
        Arrays.sort(result);
        for (Object key : result) {
            list.add(sqlPs.get("@" + key).getParaValue());
        }
        return list;
    }

    /**
     * 填充自定义变量
     *
     * @param script sql对象
     * @param paras  业务功能SQL中参数值 参数对象
     * @return SqlScript
     */
    public  SqlScript fillCustomerVariable(SqlScript script, Map<String, Object> paras) throws Exception {
        Map<String, Object> ht = paras;
        Map<String, SqlScript.SqlParaPair> sqlPs = script.getSqlParas();
        Set<String> keys = sqlPs.keySet();
        for (String key : keys) {
            if (ht.containsKey(sqlPs.get(key).getParaName().toUpperCase().trim())) {
                sqlPs.get(key).setParaValue(ht.get(sqlPs.get(key).getParaName().toUpperCase().trim()));
            }
        }
        return script;
    }

    /**
     * 填充用户变量
     *
     * @param script sql对象
     * @param uc     系统变量（缓存）
     * @return SqlScript
     */
    public  SqlScript fillUserVariables(SqlScript script, UserContext uc) throws Exception {
        Map<String, SqlScript.SqlParaPair> sqlPs = script.getSqlParas();
        Set<String> keys = sqlPs.keySet();
        for (String key : keys) {
            if (uc.getUserProperties().containsKey(sqlPs.get(key).getParaName().toString().toUpperCase().trim())) {
                sqlPs.get(key).setParaValue(uc.getUserProperties().get(sqlPs.get(key).getParaName().toString().toUpperCase().trim()));
            }
        }
        return script;
    }

    /**
     * 判断是否是单个数据源
     *
     * @param sql
     * @return boolean
     * @throws Exception
     */

    private  Boolean isSingleDataSource(String sql) throws Exception {
        if (StringUtil.isNullOrEmpty(sql)) {
            LogHelper.info("数据源sql为空");
            throw new Exception("IsSingleDataSource方法 数据源sql为空");
        } else {
            sql = sql.trim().replace(" ", "");
            if (sql.indexOf("<DataSQL>") > 0) {
                return false;
            } else {
                return true;
            }
        }
    }

    /**
     * 无调用该接口的权限
     *
     * @param funcId 业务功能SQL语句编号
     * @param uc
     * @param json
     * @return String
     * @throws Exception
     */
    public  String validateFuncPermission(Object funcId, String json, UserContext uc, String methodName) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4008);
        msg.setMsg(MsgContent.MsgValidateFuncPermission);
        msg.setResult("");
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    /**
     * 验证是否有调用该接口的权限
     *
     * @param map        请求参数
     * @param uc
     * @param json
     * @param methodName
     * @param ex
     * @return String
     * @throws Exception
     */
    public  String getDataEx(Map<String, Object> map, String json, UserContext uc, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4010);
        msg.setMsg(MsgContent.MsgGetDataEx);
        msg.setResult(ex.toString() + "\r\n" + st);
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    /**
     * 验证是否有调用该接口的权限
     *
     * @param map        请求参数
     * @param uc
     * @param json
     * @param methodName
     * @param ex
     * @return String
     * @throws Exception
     */
    public  String getDataEx(Map<String, Object> map, String json, UserContext uc, String methodName, Exception ex) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4010);
        msg.setMsg(MsgContent.MsgGetDataEx);
        msg.setResult(ex.toString());
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    /**
     * 将外部数据写入到Kayang数据库之前的准备工作
     *
     * @param funcId 业务功能SQL语句编号
     * @param uc
     * @param json   请求参数
     * @param
     * @return String
     */
    public  String createDataPrepare(String funcId, Map<String, Object> paras, UserContext uc, String json) {
        String res = "";
        String methodName = "PutData-->CreateDataPrepare";
        try {
            DataView dv = apIdataMannger.getImportTemplate(Convert.toInt32(funcId));
            if (dv.isEmpty()) {
                throw new ConfigException(-99, String.format("请检查接口编号%s的导入模板配置", funcId));
            }
            String sqlBefore = dv.get(0).getString("SQLBefore");
            int mId = prepareCreation(sqlBefore, paras, funcId, uc, dv.get(0).getInt("ID") + "");
            String msgText = getCustomMsg(mId);
            HttpMsg msg = new HttpMsg();
            msg.setMsgId(mId);
            msg.setMsg(msgText);
            msg.setResult("");
            res = JsonConvert.serializeObject(msg);

            WebAPILog log = new WebAPILog();
            log.setFuncid(Integer.parseInt(funcId));
            log.setUrid(uc.getUserID());
            log.setOpdata(json);
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(mId);
            log.setOptext(msgText);
            writeLog2DB(log);
        } catch (Exception e) {
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "CreateDataPrepare", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "CreateDataPrepare", e.toString(), new Date().toString()));
            LogHelper.error(e);
        }
        return res;
    }

    /**
     * 将外部系统的数据写入嘉扬数据库之前，执行准备工作
     *
     * @param sqlBefore
     * @param funcId    业务功能SQL语句编号
     * @param paras     业务功能SQL中参数值 参数
     * @param uc
     * @param tpid
     * @return void
     */

    private  int prepareCreation(String sqlBefore, Map<String, Object> paras, String funcId, UserContext uc, String tpid) {
        int ret = 0;

        if (!StringUtil.isNullOrEmpty(sqlBefore)) {
            try {
                Database db = DatabaseFactory.getMainDB();
                SP before = parseSP(sqlBefore, paras, uc, tpid);
                Object obj = db.execProc(before.getSpName(), before.getSpParas().toArray());

                ret = Integer.parseInt(String.valueOf(obj));

            } catch (Exception e) {
                if (ret == 0) {
                    ret = -1;
                }
                WebAPILog log = new WebAPILog();
                log.setFuncid(-1);
                log.setUrid(-1);
                log.setOpdata("");
                log.setOptime(new Date());
                log.setHostname(SystemUtil.getMachineName());
                log.setLocaluser(SystemUtil.getUserName());
                log.setOpstate(0);
                log.setOptext("PrepareCreation方法出现异常");
                writeLog2DB(log);
                LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "PrepareCreation", new Date().toString()));
                LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "PrepareCreation", e.toString(), new Date().toString()));

                e.printStackTrace();
            }
        }
        return ret;
    }

    /**
     * 封装存储过程对象
     *
     * @param sqlSP sp信息
     * @param paras 业务功能SQL中参数值 参数
     * @return SP 存储过程对象
     */
    private  SP parseSP(String sqlSP, Map<String, Object> paras, UserContext uc, String tpid) {
        // CSP_Attend_import_BF({U_URID},{PZID})
        SP sp = new SP();
        try {
            sqlSP = formate(sqlSP);
            sp.setSPParaType(getSPParaType(tpid, sqlSP));
            sqlSP = replaceSysVariable(sqlSP, uc);
            sqlSP = replaceCustomerVariable2(sqlSP, paras);
            int index = sqlSP.indexOf("(");
            if (index > 0) {
                sp.setSpName(sqlSP.substring(0, index));
                String[] spParas = sqlSP.substring(index + 1, sqlSP.length() - 1).split(",");
                List<Object> list = new ArrayList<Object>();
                for (String item : spParas) {
                    //sp参数未匹配到得置为空
                    if (item.indexOf("{") > 0) {
                        item = "";
                    }
                    if (item != null) {
                        item = item.replace("\n", "").replace("\r", "");
                        item = item.trim();
                    }
                    list.add(item);
                }
                sp.setSpParas(list);
            } else {
                sp.setSpParas(new ArrayList<Object>());
            }
        } catch (Exception e) {
            WebAPILog log = new WebAPILog();
            log.setFuncid(-1);
            log.setUrid(-1);
            log.setOpdata("");
            log.setOptime(new Date());
            log.setHostname(SystemUtil.getMachineName());
            log.setLocaluser(SystemUtil.getUserName());
            log.setOpstate(0);
            log.setOptext("ParseSP方法出现异常");
            writeLog2DB(log);
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "ParseSP", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "ParseSP", e.toString(), new Date().toString()));
            e.printStackTrace();
        }
        return sp;
    }

    /**
     * 替换自定义变量[使用]
     *
     * @param paras   业务功能SQL中参数值
     * @param _sqlTxt sql文本
     * @return String
     */
    public  String replaceCustomerVariable2(String _sqlTxt, Map<String, Object> paras) {
        String sqlTxt = StringUtil.isNullOrEmpty(_sqlTxt) ? "" : _sqlTxt;
        if (paras != null && !StringUtil.isNullOrEmpty(paras.toString())) {
            Map<String, Object> maps = paras;
            Set<String> set = maps.keySet();
            for (String key : set) {
                int pos = sqlTxt.toLowerCase().indexOf("{" + key.toLowerCase() + "}");
                while (pos > 0) {
                    sqlTxt = StringUtil.format("{0}{1}{2}", sqlTxt.substring(0, pos), maps.get(key).toString(), sqlTxt.substring(pos + 2 + key.toString().length()));
                    pos = sqlTxt.toLowerCase().indexOf("{" + key.toLowerCase() + "}");
                }
            }
        }
        return sqlTxt;
    }

    /**
     * 替换sql文本字符串里的系统变量
     *
     * @param uc      UserContext
     * @param _sqlTxt sql文本
     * @return String
     */
    public  String replaceSysVariable(String _sqlTxt, UserContext uc) {
        // U_URID;U_EID;U_CPID;U_DPID;U_JBID;U_BADGE;U_NAME;U_ENAME;U_EZID
        String sqlTxt = StringUtil.isNullOrEmpty(_sqlTxt) ? "" : _sqlTxt;
        sqlTxt = sqlTxt.toUpperCase();

    //    sqlTxt = sqlTxt.replace("{U_URID}", uc.getUserID() + "").replace("{U_EID}", !StringUtil.isNullOrEmpty(uc.getEmpID()) ? uc.getEmpName() : "").replace("{U_CPID}", !StringUtil.isNullOrEmpty(uc.getU_CPID()) ? uc.getU_CPID() : "").replace("{U_DPID}", !StringUtil.isNullOrEmpty(uc.getU_DPID()) ? uc.getU_DPID() : "").replace("{U_JBID}", !StringUtil.isNullOrEmpty(uc.getU_JBID()) ? uc.getU_JBID() : "").replace("{U_BADGE}", !StringUtil.isNullOrEmpty(uc.getU_BADGE()) ? uc.getU_BADGE() : "").replace("{U_NAME}", !StringUtil.isNullOrEmpty(uc.getU_NAME()) ? uc.getU_NAME() : "").replace("{U_ENAME}", !StringUtil.isNullOrEmpty(uc.getU_ENAME()) ? uc.getU_ENAME() : "").replace("{U_EZID}", !StringUtil.isNullOrEmpty(uc.getU_EZID()) ? uc.getU_EZID() : "");
//TODO
        return sqlTxt;
    }

    /**
     * 获取 存储过程的 参数类型
     *
     * @param sqlSP sp信息
     * @return Map sp字符串
     */
    private  Map<String, Object> getSPParaType(String tpId, String sqlSP) {
        Map<String, Object> paraTypeDic = new HashMap<String, Object>();
        String xSql = "select * from skydataimportdatacols where imid = @1";
        Database db = DatabaseFactory.getMainDB();
        DataView dv = db.query(xSql, tpId);
        int index = sqlSP.indexOf("(");
        String[] spParas = sqlSP.substring(index + 1, sqlSP.length() - 1).split(",");
        String reg = "\\{(\\w+?)\\}";
        for (String par : spParas) {
            String parName = Regex.match(par, reg).group(1);
            if (isSQLPara(par)) {
                DataColumnCollection list = dv.getColumns();
                boolean bool = false;
                if (list.contains(parName.toUpperCase())) {
                    bool = true;
                }
                if (bool && !StringUtil.isNullOrEmpty(dv.get(0))) {
                    paraTypeDic.put(par.toUpperCase(), dv.get(0).get("DTID")); // 获取实际的值
                } else {
                    paraTypeDic.put(par.toUpperCase(), -1); // 没有在表里显式定义数据类型的参数统一设置为-1
                }
            } else if (!StringUtil.isNullOrEmpty(par)) {
                paraTypeDic.put(par.toUpperCase(), -1); // 比如常量
            }
        }
        return paraTypeDic;
    }

    /**
     * 判断是否是sql参数
     *
     * @param para 业务功能SQL中参数值
     * @return Boolean
     */
    private  Boolean isSQLPara(String para) {
        if (StringUtil.isNullOrEmpty(para)) {
            return false;
        } else {
            if (para.trim().startsWith("{") && para.trim().endsWith("}")) {
                return true;
            } else {
                return false;
            }
        }
    }

    /**
     * 格式化存储过程字符串
     *
     * @param sqlSP sp信息
     * @return SP sp字符串
     */
    private  String formate(String sqlSP) {
        /*
         * StringBuilder sb = new StringBuilder(); int index =
         * sqlSP.indexOf("("); if (index > 0) { String spName =
         * sqlSP.substring(0, index); sb.append(spName); sb.append("(");
         * String[] spParas = sqlSP.substring(index + 1,
         * sqlSP.length()).split(","); for (String item : spParas) {
         * sb.append(item); sb.append("||"); } sb = new
         * StringBuilder(sb.substring(0, sb.length() - 3)); sb.append(")"); }
         * return sb.toString();
         */

        return sqlSP;
    }

    /**
     * 将外部数据写入到嘉扬系统之前的准备工作执行过程中，发生了未知异常
     *
     * @param map        请求参数json数据转换
     * @param uc
     * @param json       请求参数
     * @param methodName 方法名称
     * @param ex         异常信息
     * @return String
     */
    public  String putPrepareEx(Map<String, Object> map, String json, UserContext uc, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4042);
        msg.setMsg(MsgContent.MsgPutPrepareEx);
        msg.setResult(ex.toString() + "  ;  " + st);
        String str = JsonConvert.serializeObject(msg);
        WebAPILog log = new WebAPILog();
        if (map != null && map.get("FUNCID") != null) {
            log.setFuncid(Integer.valueOf(map.get("FUNCID").toString()));
        } else {
            log.setFuncid(-1);
        }
        log.setUrid(uc.getUserID());
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(MsgCode.Code4042);
        log.setOptext(MsgContent.MsgPutPrepareEx);
        writeLog2DB(log);
        LogHelper.info(StringUtil.format("{1}:\r\n{0};", methodName, log.toString()));
        return str;
    }

    public  String getFileByFloder(String funcId, Object dataformat, Object datapart, Map<String, Object> paras, UserContext uc, String json) throws IOException {
        DataView dv = apIdataMannger.getAPI(Convert.toInt32(funcId));
        String filepath = dv.get(0).getString("DataSource");
        byte[] data = FileUtil.getBytes(filepath);
        HttpMsg msg = new HttpMsg();
        if (data == null) {
            msg.setMsgId(-1);
            msg.setMsg("接口调用失败，未获取到文件从路径：" + filepath);
            msg.setResult("");
        } else {
            String ret = Base64.getEncoder().encodeToString(data);
            msg.setMsgId(MsgCode.Code0);
            msg.setMsg("接口调用成功，返回Result为文件的Base64字符串");
            msg.setResult(ret);
        }
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    public  String createData(String funcId, Map<String, Object> paras, List<Map<String, Object>> bizData, UserContext uc, String json) {
        return createData(funcId, paras, bizData, uc, json, false);
    }

    /**
     * 将外部数据写入到Kayang数据库
     *
     * @param funcId  业务功能SQL语句编号
     * @param uc
     * @param paras   业务功能SQL中参数值
     * @param json    请求参数
     * @param bizData 实际要插入的业务数据
     * @param isBySP  是否通过SP插入业务数据
     * @return String
     */
    public  String createData(String funcId, Map<String, Object> paras, List<Map<String, Object>> bizData, UserContext uc, String json, boolean isBySP) {
        String res = "", methodName = "PutData-->CreateData";
        DataView dv = apIdataMannger.getImportTemplate(Convert.toInt32(funcId));
        String sqlBody = dv.get(0).getString("SqlBody");
        String errMsg = "";
        String tpid = Convert.toString(dv.get(0).get("ID"));
        Object mid = null;
        try {
            if (isBySP) {
                mid = startCreationBySP(sqlBody, paras, funcId, bizData, uc, tpid);
            } else {
                mid = startCreation(sqlBody, paras, funcId, bizData, uc, tpid);
            }
        } catch (Exception e1) {
            LogHelper.error(e1);
            mid = -1;
            errMsg = e1.getMessage();
        }
        HttpMsg msg = new HttpMsg();
        int msgid = 0;
        String result = "";
        if (mid instanceof Integer) {
            msgid = Convert.toInt32(mid);
            msg.setMsgId(msgid);
            if (msgid < 0) {
                msg.setMsg(errMsg);
            } else {
                msg.setMsg(getCustomMsg(msgid));
            }
            msg.setResult("");
        } else if (mid instanceof String) {
            //字符串格式的 msgid
            if ("0".equals(mid) || StringUtil.isNullOrEmpty(mid) || Convert.toInt32(mid) > 0) {
                msgid = Convert.toInt32(mid);
                msg.setMsgId(msgid);
                if (msgid < 0) {
                    msg.setMsg(errMsg);
                } else {
                    msg.setMsg(getCustomMsg(msgid));
                }
                msg.setResult("");
            } else {
                msg.setMsgId(0);
                msg.setMsg(getCustomMsg(0));
                result = Convert.toString(mid);
                msg.setResult(result);
            }

        }

        res = JsonConvert.serializeObject(msg);

        WebAPILog log = new WebAPILog();
        log.setFuncid(Integer.parseInt(funcId));
        log.setUrid(uc.getUserID());
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(msgid);
        if (result.length() > 0) {
            log.setOptext(result);
        } else {
            log.setOptext(getCustomMsg(msgid));
        }
        writeLog2DB(log);
        return res;
    }

    private  int startCreationBySP(String sqlBody, Map<String, Object> paras, String funcId, List<Map<String, Object>> bizData, UserContext uc, String tpid) {
        //SP_PutData({PARAMS},{U_URID}, {BIZDATA},{FUNCID},{TPID})
        String sp = sqlBody;
        Map<String, Object> pMap = new HashMap<>();
        pMap.put("@PAR", JsonConvert.serializeObject(paras));
        pMap.put("@BIZDATA", JsonConvert.serializeObject(bizData));
        pMap.put("@URID", uc.getUserID());
        pMap.put("@FUNCID", funcId);
        pMap.put("@TPID", tpid);
        Database db = DatabaseFactory.getDatabase();
        Object o = db.execProc(sqlBody, pMap);
        return Convert.toInt32(o);
    }

    /// 根据MsgId来获取消息
    /// </summary>
    public  String getCustomMsg(int msgId) {
        if (msgCollection.containsKey(msgId)) {
            return msgCollection.get(msgId);
        } else {
            String xsql = "select * from skyMsgAlarm where id = @1 ";
            DataView dv = DatabaseFactory.getMainDB().query(xsql, msgId);

            if (dv != null && dv.size() > 0) {

                msgCollection.put(msgId, dv.get(0).getString("title"));
                return msgCollection.get(msgId);
            } else {
                return "未定义信息内容！";
            }
        }
    }

    /**
     * 将外部系统的数据写入嘉扬数据库，开始导入工作
     *
     * @param sqlBody 存储过程
     * @param uc
     * @param paras   业务功能SQL中参数值
     * @param bizData 实际要插入的业务数据
     * @param funcId  业务功能SQL语句编号
     * @return String
     * @throws Exception
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    private  Object startCreation(String sqlBody, Map<String, Object> paras, String funcId, List<Map<String, Object>> bizData, UserContext uc, String tpID) throws Exception {
        //bizData = !=bizData.size() ? bizData.toString() : "";
        Object obj = null;
        if (!StringUtil.isNullOrEmpty(sqlBody)) {
            Database db = DatabaseFactory.getMainDB();
            SP spBody = parseSP(sqlBody, paras, uc, tpID);
            // 如果是xml 转换 bizData 待处理
            List<Map<String, Object>> jsonList;
            jsonList = bizData;
            if (jsonList != null && jsonList.size() > 0) {
                for (int i = 0; i < jsonList.size(); i++) {
                    Map<String, Object> ht = jsonList.get(i);

                    List<Object> list = new ArrayList<Object>();
                    String reg = "\\{(\\w+?)\\}";
                    for (Object p : spBody.getSpParas()) {
                        if (isSQLPara(Convert.toString(p))) {
                            String key = Regex.match(p.toString(), reg).group(1);
                            if (ht.containsKey(key)) {
                                if (spBody.SPParaType.containsKey(p.toString().toUpperCase()) && Convert.toInt32(
                                        spBody.getSPParaType().get(Convert.toString(p).toUpperCase())) == 501) {
                                    list.add(CryptAES.base64Decode(Convert.toString(ht.get(key))));
                                } else {
                                    list.add(ht.get(key));
                                }
                            } else {
                                list.add(null); // 为传递该值时，给SQL和ORA一个默认的值.
                            }
                        } else {
                            list.add(p);
                        }
                    }

                    List<Object> list2 = new ArrayList<Object>();
                    for (int j = 0; j < list.size(); j++) {
                        if (!StringUtil.format("{0}", list.get(j)).startsWith("@")) {
                            list2.add(list.get(j));
                        }
                    }

                    obj = db.execProc(spBody.getSpName(), list2.toArray());

                }
            } else {
                obj = db.execProc(spBody.getSpName(), spBody.getSpParas().toArray());
            }
        }
        return obj;
    }

    /**
     * 写入数据的整个过程中，发生了未知异常
     * 存储过程
     *
     * @param uc 业务功能SQL中参数值
     *           实际要插入的业务数据
     * @return String
     * @throws Exception
     */
    @SuppressWarnings("rawtypes")
    public  String putDataEx(Map map, String json, UserContext uc, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4037);
        msg.setMsg(MsgContent.MsgPutDataEx);
        msg.setResult(ex.toString() + "  ;  " + st);
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    /**
     * 将外部数据写入到Kayang数据库之后的清理工作
     *
     * @param funcId 业务功能SQL语句编号
     * @param uc
     * @param paras  业务功能SQL中参数值
     * @param json
     * @return String
     */
    public  String createDataEnd(String funcId, Map<String, Object> paras, UserContext uc, String json) throws Exception {
        String res = "";
        String methodName = "PutData-->CreateDataEnd";
        int tpid = 0;
        String sqlAfter = "";
        int mId = 0;
        try {
            DataView dv = apIdataMannger.getImportTemplate(Convert.toInt32(funcId));
            sqlAfter = dv.get(0).get("SQLAfter").toString();
        } catch (Exception e) {
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "CreateDataEnd--数据库连接查询", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "CreateDataEnd--数据库连接查询", e.toString(), new Date().toString()));
            e.printStackTrace();
        }
        try {
            mId = endCreation(sqlAfter, paras, funcId, uc, tpid + "");
        } catch (Exception e) {
            LogHelper.info(StringUtil.format(" {0} 方法出现异常  ;\r\n 时间 : {1}", "CreateDataEnd--EndCreation", new Date().toString()));
            LogHelper.error(StringUtil.format(" {0} 方法出现异常  ;\r\n 异常信息:{1}; \r\n时间 : {2}", "CreateDataEnd--EndCreation", e.toString(), new Date().toString()));
            mId = -1;
        }
        HttpMsg msg = new HttpMsg();
        if (mId == -1) {
            msg.setMsgId(MsgCode.Code4040);
            msg.setMsg(MsgContent.MsgEndCreationErr);
            msg.setResult("");
            res = JsonConvert.serializeObject(msg);
        } else {
            msg.setMsgId(mId);
            msg.setMsg(getCustomMsg(mId));
            msg.setResult("");
            res = JsonConvert.serializeObject(msg);
        }

        WebAPILog log = new WebAPILog();
        log.setFuncid(Integer.parseInt(funcId));
        log.setUrid(uc.getUserID());
        log.setOpdata(json);
        log.setOptime(new Date());
        log.setHostname(SystemUtil.getMachineName());
        log.setLocaluser(SystemUtil.getUserName());
        log.setOpstate(mId);
        log.setOptext(getCustomMsg(mId));
        writeLog2DB(log);
        return res;
    }

    /**
     * 将外部系统的数据写入嘉扬数据库之后，执行后续工作
     *
     * @param funcId   业务功能SQL语句编号
     * @param uc
     * @param paras    业务功能SQL中参数值
     * @param sqlAfter
     * @return String
     */
    private  int endCreation(String sqlAfter, Map<String, Object> paras, String funcId, UserContext uc, String tpid) throws Exception {
        int ret = 0;
        if (!StringUtil.isNullOrEmpty(sqlAfter)) {
            Database db = DatabaseFactory.getMainDB();
            SP after = parseSP(sqlAfter, paras, uc, tpid);
            Object obj = db.execProc(after.getSpName(), after.getSpParas().toArray());
            ret = Integer.parseInt(String.valueOf(obj));
			/*if (obj.toString().equals("0")) {
				flag = true;
			} else {
				flag = false;
			}*/
        }
        return ret;
    }

    /**
     * 将外部数据写入到嘉扬系统之后的清理工作执行过程中，发生了未知异常
     * <p>
     * 业务功能SQL语句编号
     *
     * @param uc 业务功能SQL中参数值
     * @return String
     */
    @SuppressWarnings("rawtypes")
    public  String putEndEx(Map map, String json, UserContext uc, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4043);
        msg.setMsg(MsgContent.MsgPutEndEx);
        msg.setResult(ex.toString() + "   ;   " + st);
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

//    /**
//     * 验证HttpFiles集合的有效性
//     *
//     * @param funcId     业务功能SQL语句编号
//     * @param uc         全局变量信息
//     * @param hfc        上传文件信息
//     * @param methodName 方法名称
//     * @return String
//     */
//    public static String validateHttpFiles(List<FileItem> hfc, Object funcId, UserContext uc, String methodName) {
//        flag = true;
//        String str = "";
//        if (hfc == null || hfc.size() == 0) {
//            flag = false;
//            HttpMsg msg = new HttpMsg();
//            msg.setMsgId(MsgCode.Code4050);
//            msg.setMsg("上传文件为空");
//            msg.setResult("");
//            str = JsonConvert.serializeObject(msg);
//        }
//        return str;
//    }

    /**
     * 获取参数异常
     *
     * @return String
     */
    public  String getParamEx(UserContext uc, Exception ex, String st) {
        String str = "";
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4003);
        msg.setMsg("参数解析出现异常");
        msg.setResult(ex.toString() + "   ;   " + st);
        str = JsonConvert.serializeObject(msg);
        return str;
    }

    /**
     * 上传文件的整个过程中，发生了未知异常
     *
     * @param map
     * @param json
     * @param uc
     * @param methodName
     * @param ex
     * @return String
     */
    @SuppressWarnings("rawtypes")
    public  String putFileEx(Map map, String json, UserContext uc, String methodName, Exception ex, String st) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code4050);
        msg.setMsg(MsgContent.MsgPutFileException);
        msg.setResult(ex.toString() + "  ;  " + st);
        String str = JsonConvert.serializeObject(msg);
        return str;
    }

    public  String rightTrim(String str, String s) {
        if (str.substring(str.length() - 1, str.length()).equals(s)) {
            str = str.substring(0, str.length() - 1);
        }
        return str;
    }

    public  String leftTrim(String str, String s) {
        if (str.substring(0, 1).equals(s)) {
            str = str.substring(1, str.length());
        }
        return str;
    }


    @Autowired
    WebApiConfig webApiConfig;

    @Value("${webapi.bsTokenTimeout}")
    private  int bsTokenTimeout ;


    /// <summary>
    /// 根据一个DataRowView,构建一个新的DataView
    /// </summary>
    private  DataView buildOneView(DataRowView drv) {
        DataTable dt = new DataTable();
        dt.addColumn("id", Integer.class);
        dt.addColumn("Account", String.class);
        dt.addColumn("Password", String.class);
        dt.addRow(drv.get("id"), drv.get("Account"), drv.get("Password"));
        return dt.getDefaultView();
    }


    public  String accessTokenSuccess(String token, String json, String methodName, int urid) {
        HttpMsg msg = new HttpMsg();
        msg.setMsgId(MsgCode.Code0);
        msg.setMsg(MsgContent.MsgToken); //MsgContent.MsgToken;
        msg.setResult(token);

        String str = JsonConvert.serializeObject(msg);

        WebAPILog log = new WebAPILog();
        log.setId(-1);
        log.setUrid(urid);
        log.setOpdata(json); //OPData = json;
        log.setOptime(new Date()); //= new Date();
        log.setHostname(WebHelper.getClientHostName(HttpContext.getCurrent().getServletRequest()));
        log.setLocaluser(WebHelper.getClientMac(HttpContext.getCurrent().getServletRequest())); //WebHelper.GetClientUser(HttpContext.Current, dv[0]["ID"].ToString()) ?? "";
        log.setOpstate(MsgCode.Code0);
        log.setOptext(MsgContent.MsgToken); //MsgContent.MsgToken;

        writeLog2DB(log);
        LogHelper.info(StringUtil.format("{3}:\r\n{0};AccessToken:{1};JSON参数:{2}", log.toString(), token, json, methodName));

        return str;
    }

}
